import xlrd
from app.models import *

# 读取excel数据
def read_xls_cls(filename):
    data = xlrd.open_workbook(filename)
    sheets = data.sheets()
    data_list = []
    for sheet in sheets:
        lines = []
        name = sheet.name
        for r in range(sheet.nrows):
            col = []
            for c in range(sheet.ncols):
                col.append(sheet.cell(r, c).value)
            lines.append(col)
        data_list.append([name, lines])
    return data_list

# 插入2020-2023年邯郸市公务员岗位报考相关数据
class InsertCls():
    def __init__(self):
        self.major_list = []  #专业
        self.job_list = [] # 岗位
        self.jobCode_dict = {} # 岗位

    #岗位信息
    def job_cls(self):
        job_data_list = read_xls_cls("data/邯郸市岗位表2020-2023.xlsx")
        for year, lines in job_data_list:
            for line in lines[1:]:
                jobCode = str(int(line[0]))
                city = line[1].strip()
                unit = line[2].strip()
                position = line[3].strip()
                recruitNum = int(line[4])
                education = line[5].strip()
                degree = line[6].strip()
                ask = line[8].strip()
                major = line[7].strip()
                self.jobCode_dict[jobCode] = {}
                if major:
                    major_list = major.split("、")
                    if major_list:
                        self.jobCode_dict[jobCode]["major_list"] = major_list
                    for major in major_list:
                        if major not in self.major_list:
                            self.major_list.append(major)
                self.job_list.append([
                    year, jobCode, city, unit, position, recruitNum, education, degree, ask
                ])

    # 报名统计表
    def enroll_cls(self):
        enroll_data_list = read_xls_cls("data/邯郸市岗位报名统计表2020-2023.xlsx")
        for year, lines in enroll_data_list:
            for line in lines[1:]:
                jobCode = str(int(line[0]))
                enrollNum = int(line[1])
                checkNum = int(line[2])
                checkPassNum = int(line[3])
                payNum = int(line[4])
                self.jobCode_dict[jobCode]["enrollNum"] = enrollNum
                self.jobCode_dict[jobCode]["checkNum"] = checkNum
                self.jobCode_dict[jobCode]["checkPassNum"] = checkPassNum
                self.jobCode_dict[jobCode]["payNum"] = payNum

    # 考试成绩
    def student_cls(self):
        stu_data_list = read_xls_cls("data/考试成绩2020-2023.xlsx")
        for year, lines in stu_data_list:
            for line in lines[1:]:
                jobCode = str(int(line[0]))
                name = line[2]
                ids = line[3]
                score = float(line[4])
                #group = line[5].strip()
                rank = int(line[5])
                self.jobCode_dict[jobCode][ids] = {
                    "name": name,
                    "score": score,
                    #"group": group,
                    "rank": rank
                }
                if "ids" not in self.jobCode_dict[jobCode]:
                    self.jobCode_dict[jobCode]["ids"] = []
                self.jobCode_dict[jobCode]["ids"].append(ids)


    # 保存数据库
    def save_data_cls(self):
        # 保存专业信息
        for major in self.major_list:
            major_tab = MajorTab.objects.filter(major=major)
            if major_tab.count() == 0:
                MajorTab(major=major).save()
        # 岗位表信息
        for data in self.job_list:
            year = data[0]
            jobCode = data[1]
            city = data[2]
            unit = data[3]
            position = data[4]
            recruitNum = data[5]
            education = data[6]
            degree = data[7]
            ask = data[8]
            enrollNum = 0
            checkNum = 0
            checkPassNum = 0
            payNum = 0
            print(year, jobCode)
            if jobCode in self.jobCode_dict:
                if "enrollNum" in self.jobCode_dict[jobCode]:
                    enrollNum = self.jobCode_dict[jobCode]["enrollNum"]
                    checkNum = self.jobCode_dict[jobCode]["checkNum"]
                    checkPassNum = self.jobCode_dict[jobCode]["checkPassNum"]
                    payNum = self.jobCode_dict[jobCode]["payNum"]
                else:
                    enrollNum = 0
                    checkNum = 0
                    checkPassNum = 0
                    payNum = 0
            job_tab = JobTab.objects.filter(jobCode=jobCode)
            if job_tab.count() == 0:
                job_tab = JobTab(
                    year = year,
                    jobCode = jobCode,
                    city = city,
                    unit = unit,
                    position = position,
                    recruitNum = recruitNum,
                    education = education,
                    degree = degree,
                    ask = ask,
                    enrollNum = enrollNum,
                    checkNum = checkNum,
                    checkPassNum = checkPassNum,
                    payNum = payNum
                )
                job_tab.save()
                job_id = job_tab.id
                if "major_list" in self.jobCode_dict[jobCode]:
                    major_list = self.jobCode_dict[jobCode]["major_list"]
                    major_list = MajorTab.objects.filter(major__in=major_list)
                    for ma in major_list:
                        major_id = ma.id
                        JobMajorTab_tab = JobMajorTab(
                            job_id=job_id, major_id=major_id
                        )
                        JobMajorTab_tab.save()
                if "ids" in self.jobCode_dict[jobCode]:
                    ids_list = self.jobCode_dict[jobCode]["ids"]
                    for ids in ids_list:
                        ids_data = self.jobCode_dict[jobCode][ids]
                        StudentTab_tab = StudentTab.objects.filter(ids=ids)
                        if StudentTab_tab.count() == 0:
                            StudentTab_tab = StudentTab(
                                job_id=job_id,
                                name = ids_data["name"],
                                ids=ids,
                                score=ids_data["score"],
                                rank=ids_data["rank"]
                            )
                            StudentTab_tab.save()

    def main(self):
        self.job_cls()
        self.enroll_cls()
        self.student_cls()
        self.save_data_cls()

